package org.jdbc_test.dao.impl;

import org.jdbc_test.dao.StaffsDao;
import org.jdbc_test.entity.Staffs;
import org.jdbc_test.util.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ZJL
 * @date 2025-02-24
 */
public class StaffsDaoImpl implements StaffsDao {
    @Override
    public List<Staffs> staffsListLimit(Integer limit, Integer offset) {
        List<Staffs> list = new ArrayList<Staffs>();
        String sql = "select id,name,age,phone,sta_pos,add_time,update_time from staffs limit ?,?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = DbUtils.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, limit);
            ps.setInt(2, offset);
            rs = ps.executeQuery();
            while (rs.next()) {
                Staffs staff = new Staffs();
                staff.setId(rs.getInt("id"));
                staff.setName(rs.getString("name"));
                staff.setAge(rs.getInt("age"));
                staff.setPhone(rs.getString("phone"));
                staff.setStaPos(rs.getString("sta_pos"));
                staff.setAddTime(rs.getTimestamp("add_time"));
                staff.setUpdateTime(rs.getTimestamp("update_time"));
                list.add(staff);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }

    @Override
    public int addStaffs(Staffs staffs) {
        int row = 0;
        String sql = "insert into staffs(name,age,phone,sta_pos) values (?,?,?,?);";
        PreparedStatement ps = null;
        Connection conn = DbUtils.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, staffs.getName());
            ps.setInt(2, staffs.getAge());
            ps.setObject(3, staffs.getPhone());
            ps.setString(4, staffs.getStaPos());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }

    @Override
    public int updateStaffs(Staffs staffs) {
        int row = 0;
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=?,update_time=? where id=?";
        PreparedStatement ps = null;
        Connection conn = DbUtils.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, staffs.getName());
            ps.setInt(2, staffs.getAge());
            ps.setObject(3, staffs.getPhone());
            ps.setString(4, staffs.getStaPos());
            ps.setObject(5, staffs.getUpdateTime());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }

    @Override
    public int deleteStaffs(int id) {
        int row = 0;
        String sql = "delete from staffs where id=?";
        Connection conn = DbUtils.getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            row = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return row;
    }

    @Override
    public Staffs getStaffsLimit(int id,Integer limit,Integer offset) {
        Staffs staffs = null;
        String sql = "select id,name,age,phone,sta_pos,add_time,update_time from staffs where id=? limit ?,?";
        Connection conn = DbUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.setInt(2, limit);
            ps.setInt(3, offset);
            rs = ps.executeQuery();
            if (rs.next()) {
                staffs = new Staffs();
                staffs.setId(rs.getInt("id"));
                staffs.setName(rs.getString("name"));
                staffs.setAge(rs.getInt("age"));
                staffs.setPhone(rs.getString("phone"));
                staffs.setStaPos(rs.getString("sta_pos"));
                staffs.setAddTime(rs.getTimestamp("add_time"));
                staffs.setUpdateTime(rs.getTimestamp("update_time"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return staffs;
    }
}
